
package DAO;

/**
 * This class represents abstraction for data access from administrator table in database.
 * This class is called from DAO as a proxy.
 * @see DAO
 * @Date Mar 30, 2011
 * @author Tony Lei
 */
import DTO.Item;
import DTO.Admin;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.*;
import Exception.*;

public class AdminDAO {

    private Connection conn;
    private static final Logger logger = Logger.getLogger(AdminDAO.class.getName());

     /**
     * constructor for this class.
     * @param conn connection object
     */
    public AdminDAO(Connection conn) {
        this.conn = conn;
    }

    /**
     * This method retrieves administrator information from database
     * @param adminid an integer
     * @return AdminDTO object
     * @throws {@link KwickKioskDBException}
     */
    public Admin getAdmin(int adminid) throws KwickKioskDBException {
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from administrator a "
                    + "where a.adminid=" + adminid;
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            if (rs.next()) {
                Admin theAdmin = new Admin(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4));
                rs.close();
                sqlStatement.close();
                return theAdmin;
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "AdminDAO : getAdmin" + ex);
            throw new KwickKioskDBException("AdminDAO : getAdmin", ex.getMessage());
        }
        return null;
    }

    /**
     * This method searches an item in database by item name as criteria
     * @param keywords string representing search criteria
     * @return array list object of item data transfer objects
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Item> searchByName(String keywords)
            throws KwickKioskDBException {
        ArrayList<Item> list = new ArrayList<Item>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from item it ";
            StringTokenizer st = new StringTokenizer(keywords.trim());

            if (st.hasMoreTokens()) {
                sqlText += "where ";
            }

            while (st.hasMoreTokens()) {
                sqlText += "it.itemName like '%" + st.nextToken() + "%'";
                if (st.hasMoreTokens()) {
                    sqlText += " and ";
                }
            }

            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "AdminDAO : searchByName" + ex);
            throw new KwickKioskDBException("AdminDAO : searchByName",
                    ex.getMessage());
        }
        return list;
    }

    /**
     * This method searches item in database by item code as search criteria
     * @param upc a string for item code
     * @return item data transfer object
     * @throws {@link KwickKioskDBException}
     */
    public Item searchByUPC(String upc) throws KwickKioskDBException {
        Item result = null;
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from item it "
                    + "where it.upc='" + upc + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            if (rs.next()) {
                Item newItem = new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9));
                sqlStatement.close();
                return newItem;
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "AdminDAO : searchByUPC" + ex);
            throw new KwickKioskDBException("AdminDAO : searchByUPC",
                    ex.getMessage());
        }
        return null;
    }

    /**
     * This method retrieves item information from database using item manufacturer as criteria.
     * @param make a string representing item manufacturer
     * @return an array list object of item data transfer objects
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Item> searchByMake(String make)
            throws KwickKioskDBException {
        ArrayList<Item> list = new ArrayList<Item>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from item it "
                    + "where it.make='" + make + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "AdminDAO : searchByMake" + ex);
            throw new KwickKioskDBException("AdminDAO : searchByMake",
                    ex.getMessage());
        }
        return list;
    }

    /**
     * This method searches item in database using item model as criteria.
     * @param model a string
     * @return an array list object of items
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Item> searchByModel(String model)
            throws KwickKioskDBException {
        ArrayList<Item> list = new ArrayList<Item>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from item it "
                    + "where it.model=" + model;
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "AdminDAO : searchByModel" + ex);
            throw new KwickKioskDBException("AdminDAO : searchByModel",
                    ex.getMessage());
        }
        return list;
    }

    /**
     * This method searches item in database using a keyword search.
     * @param keywords a string
     * @return an array list object of items
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Item> searchByDescription(String keywords)
            throws KwickKioskDBException {
        ArrayList<Item> list = new ArrayList<Item>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from item it ";
            StringTokenizer st = new StringTokenizer(keywords.trim());

            if (st.hasMoreTokens()) {
                sqlText += "where ";
            }

            while (st.hasMoreTokens()) {
                sqlText += "it.itemName like '%" + st.nextToken() + "%'";
                if (st.hasMoreTokens()) {
                    sqlText += " and ";
                }
            }

            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "AdminDAO : searchByDescription" + ex);
            throw new KwickKioskDBException("AdminDAO : searchByDescription",
                    ex.getMessage());
        }
        return list;
    }

    /**
     * This method searches item based on a category id.
     * @param ctid  an integer
     * @return array list of items
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Item> getItemsByCategory(int ctid)
            throws KwickKioskDBException {
        ArrayList<Item> list = new ArrayList<Item>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from item it "
                    + "where it.ctid in ("
                    + "select temp.ctid "
                    + "from ("
                    + "select c.ctid "
                    + "from category c "
                    + "where c.ctid not in ("
                    + "select distinct c1.pctid "
                    + "from category c1 "
                    + "where c1.pctid is not NULL)) temp "
                    + "where temp.ctid like '" + ctid + "%')";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "AdminDAO : getItemByCategory" + ex);
            throw new KwickKioskDBException("AdminDAO : getItemByCategory",
                    ex.getMessage());
        }

        return list;
    }

    /**
     * This method searches items in database by a category id and brand.
     * @param ctid an integer category id
     * @param brand a string  brand
     * @return an array list of objects
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Item> getItemsByCategoryAndBrand(int ctid, String brand)
            throws KwickKioskDBException {
        ArrayList<Item> list = new ArrayList<Item>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from item it "
                    + "where it.make='" + brand + "' and " + "it.ctid in ("
                    + "select temp.ctid "
                    + "from ("
                    + "select c.ctid "
                    + "from category c "
                    + "where c.ctid not in ("
                    + "select distinct c1.pctid "
                    + "from category c1 "
                    + "where c1.pctid is not NULL)) temp "
                    + "where temp.ctid like '" + ctid + "%')";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null,
                    "AdminDAO : getItemByCategoryAndCategory" + ex);
            throw new KwickKioskDBException(
                    "AdminDAO : getItemByCategoryAndCategory",
                    ex.getMessage());
        }

        return list;
    }

    /**
     * This method searches items based on a price range criteria.
     * @param ctid an integer
     * @param lower a double value for lower limit
     * @param upper a double value for upper limit
     * @return a list of item objects
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Item> getItemsByPriceRange(int ctid,
            double lower, double upper) throws KwickKioskDBException {
        ArrayList<Item> list = new ArrayList<Item>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from item it where ";

            if (upper != -1.0) {
                sqlText += "it.retailPrice >= " + lower
                        + " and " + "it.retailPrice <= " + upper
                        + " and ";
            } else {
                sqlText += "it.retailPrice >= " + lower + " and ";
            }

            sqlText += "it.ctid in ("
                    + "select temp.ctid "
                    + "from ("
                    + "select c.ctid "
                    + "from category c "
                    + "where c.ctid not in ("
                    + "select distinct c1.pctid "
                    + "from category c1 "
                    + "where c1.pctid is not NULL)) temp "
                    + "where temp.ctid like '" + ctid + "%')";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "AdminDAO : getItemByPriceRange" + ex);
            throw new KwickKioskDBException("AdminDAO : getItemByPriceRange",
                    ex.getMessage());
        }

        return list;
    }

    /**
     * This method retrieves all items in database
     * @return an array list of items
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Item> getAllItems() throws KwickKioskDBException {
        ArrayList<Item> list = new ArrayList<Item>();
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from item";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Item(rs.getString(1),
                        rs.getString(2),
                        rs.getDouble(3),
                        rs.getDouble(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8),
                        rs.getInt(9)));
            }
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "AdminDAO : getAllItems" + ex);
            throw new KwickKioskDBException("AdminDAO : getAllItems",
                    ex.getMessage());
        }
        return list;
    }

    /**
     * This method retrieves a item data
     * @param upc a string item codes
     * @param startDate a string for start date as criteria
     * @param endDate  a string for end date as criteria
     * @return  a vectored object as result 
     * @throws {@link KwickKioskDBException}
     */
    public Vector<String> getItemStat(String upc, String startDate, String endDate)
            throws KwickKioskDBException {

        Statement sqlStatement;
        Vector<String> stat = new Vector<String>();
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select i.upc, i.itemName, sum(io.qty) as qty, "
                    + "sum(io.soldPrice) as sales, "
                    + "sum(io.soldPrice) - sum((i.costPrice * io.qty)) as profit "
                    + "from item i, inorder io, orders o "
                    + "where i.upc=io.upc and io.oid=o.oid and i.upc='" + upc + "' "
                    + "and '" + startDate + "' <= o.orderDate and o.orderDate <= '"
                    + endDate + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            if (rs.next()) {
                stat = new Vector<String>();
                stat.add(rs.getString(1));
                stat.add(rs.getString(2));
                stat.add("" + rs.getInt(3));
                stat.add("" + rs.getDouble(4));
                stat.add("" + rs.getDouble(5));
            }
            rs.close();
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ReportDAO : getItemStat" + ex);
            throw new KwickKioskDBException("ReportDAO : getItemStat",
                    ex.getMessage());
        }

        return stat;
    }

    /**
     * This method retrieves a report data.
     * @param storeName string for name of store
     * @param startDate  a string for start date as criteria
     * @param endDate a string for end date as criteria
     * @return a vectored object as result 
     * @throws {@link KwickKioskDBException}
     */
    public Vector<String> getStoreStat(String storeName, String startDate, String endDate)
            throws KwickKioskDBException {

        Statement sqlStatement;
        Vector<String> stat = new Vector<String>();
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select o.storeName, count(distinct o.oid) as orders, "
                    + "sum(io.soldPrice) as sales, "
                    + "sum(io.soldPrice) - sum(i.costPrice*io.qty) as profit "
                    + "from item i, inorder io, orders o "
                    + "where i.upc=io.upc and io.oid=o.oid and o.storeName='"
                    + storeName + "'" + " and '" + startDate
                    + "' <= o.orderDate and o.orderDate <= '" + endDate + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            if (rs.next()) {
                stat = new Vector<String>();
                if (rs.getString(1) == null) {
                    stat.add(storeName);
                } else {
                    stat.add(rs.getString(1));
                }
                stat.add("" + rs.getInt(2));
                stat.add("" + rs.getDouble(3));
                stat.add("" + rs.getDouble(4));
            }
            rs.close();
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ReportDAO : getStoreStat" + ex);
            throw new KwickKioskDBException("ReportDAO : getStoreStat",
                    ex.getMessage());
        }

        return stat;
    }

    /**
     * This method retrieves a report data
     * @param postCode a string for postal code
     * @param startDate a string for start date as criteria
     * @param endDate  a string for end date as criteria
     * @return a vectored object as result 
     * @throws {@link KwickKioskDBException}
     */
    public Vector<String> getAreaStat(String postCode, String startDate, String endDate)
            throws KwickKioskDBException {

        Statement sqlStatement;
        Vector<String> stat = new Vector<String>();
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select a.postCode, count(distinct o.csid) as customers, "
                    + "sum(io.soldPrice) as sales, "
                    + "sum(io.soldPrice) - sum(i.costPrice*io.qty) as profit "
                    + "from item i, inorder io, orders o, "
                    + "address a "
                    + "where i.upc=io.upc and io.oid=o.oid and o.aid=a.aid "
                    + "and a.postCode='" + postCode + "' and '" + startDate
                    + "' <= o.orderDate and o.orderDate <= '" + endDate + "'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            if (rs.next()) {
                stat = new Vector<String>();
                if (rs.getString(1) == null) {
                    stat.add(postCode);
                } else {
                    stat.add(rs.getString(1));
                }
                stat.add("" + rs.getInt(2));
                stat.add("" + rs.getDouble(3));
                stat.add("" + rs.getDouble(4));
            }
            rs.close();
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ReportDAO : getAreaStat" + ex);
            throw new KwickKioskDBException("ReportDAO : getAreaStat",
                    ex.getMessage());
        }

        return stat;
    }

    /**
     * This method retrieves report data for inventory
     * @param storeName a string for store name
     * @return a vectored object as result 
     * @throws {@link KwickKioskDBException}
     */
    public Vector<Vector> getInventoryReport(String storeName)
            throws KwickKioskDBException {

        Statement sqlStatement;
        Vector<Vector> data = new Vector<Vector>();
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select i.upc, i.itemName, it.qty, sum(io.qty) "
                    + "from iteminstore it, item i,"
                    + "inorder io, " + "orders o "
                    + "where i.upc=it.upc and it.storeName='" + storeName
                    + "' and it.storeName=o.storeName and it.qty<100 "
                    + "and o.oid=io.oid and io.upc=i.upc and o.orderDate >= curdate()-10 "
                    + "group by i.upc, i.itemName, it.qty";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            Vector<String> stat = new Vector<String>();
            while (rs.next()) {
                stat = new Vector<String>();
                stat.add(rs.getString(1));
                stat.add("" + rs.getString(2));
                stat.add("" + rs.getInt(3));
                stat.add("" + rs.getInt(4));
                data.add(stat);
            }
            rs.close();
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ReportDAO : getInventoryReport" + ex);
            throw new KwickKioskDBException("ReportDAO : getInventoryReport",
                    ex.getMessage());
        }

        return data;
    }

    /**
     * This method retrieves postal codes for stores
     * @return a vectored object as result
     * @throws {@link KwickKioskDBException}
     */
    public Vector<Vector> getPostCode() throws KwickKioskDBException {

        Statement sqlStatement;
        Vector<Vector> records = new Vector<Vector>();
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select a.postCode, a.city, a.province "
                    + "from address a "
                    + "group by a.postCode, a.city, a.province";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            Vector<String> record;
            while (rs.next()) {
                record = new Vector<String>();
                record.add(rs.getString(1));
                record.add("" + rs.getString(2));
                record.add("" + rs.getString(3));
                records.add(record);
            }
            rs.close();
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ReportDAO : getPostCodes" + ex);
            throw new KwickKioskDBException("ReportDAO : getPostCodes",
                    ex.getMessage());
        }

        return records;
    }

    /**
     * This method retrieves all postal codes for a city
     * @param city a string
     * @return a vectored object as result 
     * @throws {@link KwickKioskDBException}
     */
     public Vector<Vector> getPostCodeByCity(String city)
             throws KwickKioskDBException {

        Statement sqlStatement;
        Vector<Vector> records = new Vector<Vector>();
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select a.postCode, a.city, a.province "
                    + "from address a "
                    + "where a.city=" + city + " "
                    + "group by a.postCode, a.city, a.province";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            Vector<String> record;
            while (rs.next()) {
                record = new Vector<String>();
                record.add(rs.getString(1));
                record.add("" + rs.getString(2));
                record.add("" + rs.getString(3));
                records.add(record);
            }
            rs.close();
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ReportDAO : getPostCodesByCity"
                    + ex);
            throw new KwickKioskDBException("ReportDAO : getPostCodesByCity",
                    ex.getMessage());
        }

        return records;
    }

     /**
      * This method retrieves all postal codes for a province
      * @param province a string
      * @return a vectored object as result
      * @throws {@link KwickKioskDBException}
      */
     public Vector<Vector> getPostCodeByProvince(String province)
             throws KwickKioskDBException {

        Statement sqlStatement;
        Vector<Vector> records = new Vector<Vector>();
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select a.postCode, a.city, a.province "
                    + "from address a "
                    + "where a.province=" + province + " "
                    + "group by a.postCode, a.city, a.province";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            Vector<String> record;
            while (rs.next()) {
                record = new Vector<String>();
                record.add(rs.getString(1));
                record.add("" + rs.getString(2));
                record.add("" + rs.getString(3));
                records.add(record);
            }
            rs.close();
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ReportDAO : getPostCodesByProvince"
                    + ex);
            throw new KwickKioskDBException("ReportDAO : getPostCodesByProvince",
                    ex.getMessage());
        }

        return records;
    }

     /**
      * This method searches a store based on postal code
      * @param code  a string for postal code
      * @return a vectored object as result
      * @throws {@link KwickKioskDBException}
      */
     public Vector<Vector> searchPostCode(String code)
             throws KwickKioskDBException {

        Statement sqlStatement;
        Vector<Vector> records = new Vector<Vector>();
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select a.postCode, a.city, a.province "
                    + "from address a "
                    + "where a.postCode=" + code + " "
                    + "group by a.postCode, a.city, a.province";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            Vector<String> record;
            while (rs.next()) {
                record = new Vector<String>();
                record.add(rs.getString(1));
                record.add("" + rs.getString(2));
                record.add("" + rs.getString(3));
                records.add(record);
            }
            rs.close();
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ReportDAO : searchPostCode"
                    + ex);
            throw new KwickKioskDBException("ReportDAO : searchPostCode",
                    ex.getMessage());
        }

        return records;
    }

     /**
      * This method retrieves data required to view chart for items
      * @param upc a string for item code
      * @param startDate a string for start date
      * @param endDate  a string
      * @return a vectored object as result 
      * @throws {@link KwickKioskDBException}
      */
     public Vector<Vector<String>> getItemChartStat(String upc,
                                                 String startDate,
                                                 String endDate)
    throws KwickKioskDBException {

    Statement sqlStatement;
    Vector<Vector<String>> stat = new Vector<Vector<String>>();
    try {
      sqlStatement = conn.createStatement();
      String sqlText = "select i.upc, i.itemName, o.orderDate, sum(io.soldPrice) as sales "
        + "from item i, inorder io, orders o "
        + "where i.upc=io.upc and io.oid=o.oid "
        + "and i.upc='" + upc + "' "
        + "and '" + startDate + "' <= o.orderDate and o.orderDate <= '"
        + endDate + "' group by o.orderDate";
      ResultSet rs = sqlStatement.executeQuery(sqlText);
      Vector<String> data = new Vector<String>();
      while (rs.next()) {
        data = new Vector<String>();
        data.add(rs.getString(1));
        data.add(rs.getString(2));
        data.add("" + rs.getString(3));
        data.add("" + rs.getDouble(4));
        stat.add(data);
      }
      rs.close();
      sqlStatement.close();
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, null, "ReportDAO : getItemChartStat" + ex);
      throw new KwickKioskDBException("ReportDAO : getItemChartStat",
                    ex.getMessage());
    }

    return stat;
  }

     /**
      * This method retrieves data required to view chart for items.
      * @param upc a string for item code.
      * @param startDate a string for start date
      * @param endDate a string
      * @return a vectored object as result
      * @throws {@link KwickKioskDBException}
      */
  public Vector<Vector<String>> getItemChartMthStat(String upc,
                                                    String startDate,
                                                    String endDate)
    throws KwickKioskDBException {

    Statement sqlStatement;
    Vector<Vector<String>> stat = new Vector<Vector<String>>();
    try {
      sqlStatement = conn.createStatement();
      String sqlText = "select i.upc, i.itemName, month(o.orderDate) as mth, "
        + "year(o.orderDate) as yr, sum(io.qty) as qty, "
        + "sum(io.soldPrice) as sales "
        + "from item i, inorder io, orders o "
        + "where i.upc=io.upc and io.oid=o.oid "
        + "and i.upc='" + upc + "' "
        + "and '" + startDate + "' <= o.orderDate and o.orderDate <= '"
        + endDate + "' group by mth, yr";
      ResultSet rs = sqlStatement.executeQuery(sqlText);
      Vector<String> data = new Vector<String>();
      while (rs.next()) {
        data = new Vector<String>();
        data.add(rs.getString(1));
        data.add(rs.getString(2));
        data.add(rs.getString(3));
        data.add(rs.getString(4));
        data.add(rs.getString(6));
        stat.add(data);
      }
      rs.close();
      sqlStatement.close();
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, null, "ReportDAO : getItemChartMthStat" + ex);
      throw new KwickKioskDBException("ReportDAO : getItemChartMthStat",
                    ex.getMessage());
    }

    return stat;
  }

  /**
   * This method retrieves data required to view chart for items
   * @param upc a string for item code
   * @param startDate a string for start date
   * @param endDate a string
   * @return  a vectored object as result 
   * @throws {@link KwickKioskDBException}
   */
  public Vector<Vector<String>> getItemChartYearStat(String upc,
                                                     String startDate,
                                                     String endDate)
    throws KwickKioskDBException {

    Statement sqlStatement;
    Vector<Vector<String>> stat = new Vector<Vector<String>>();
    try {
      sqlStatement = conn.createStatement();
      String sqlText = "select i.upc, i.itemName, year(o.orderDate) as yr, "
        + "sum(io.soldPrice) as sales "
        + "from digistore.item i, digistore.inOrder io, digistore.orders o "
        + "where i.upc=io.upc and io.oid=o.oid "
        + "and i.upc='" + upc + "' "
        + "and '" + startDate + "' <= o.orderDate and o.orderDate <= '"
        + endDate + "'";
      ResultSet rs = sqlStatement.executeQuery(sqlText);
      Vector<String> data = new Vector<String>();
      while (rs.next()) {
        data = new Vector<String>();
        data.add(rs.getString(1));
        data.add(rs.getString(2));
        data.add(rs.getString(3));
        data.add(rs.getString(4));
        stat.add(data);
      }
      rs.close();
      sqlStatement.close();
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, null, "ReportDAO : getItemChartYearStat" + ex);
      throw new KwickKioskDBException("ReportDAO : getItemChartYearStat",
                    ex.getMessage());
    }

    return stat;
  }

  /**
   * This method retrieves data required to view chart for store
   * @param store string for name of store
   * @param startDate a string for start date
   * @param endDate a string
   * @return a vectored object as result 
   * @throws {@link KwickKioskDBException}
   */
  public Vector<Vector<String>> getStoreChartStat(String store,
                                                  String startDate,
                                                  String endDate)
    throws KwickKioskDBException {

    Statement sqlStatement;
    Vector<Vector<String>> stat = new Vector<Vector<String>>();
    try {
      sqlStatement = conn.createStatement();
      String sqlText = "select o.storeName, o.orderDate, sum(io.soldPrice) as sales "
        + "from item i, inorder io, orders o "
        + "where i.upc=io.upc and io.oid=o.oid "
        + "and o.storeName='" + store + "' "
        + "and '" + startDate + "' <= o.orderDate and o.orderDate <= '"
        + endDate + "' " + "group by o.orderDate";
      ResultSet rs = sqlStatement.executeQuery(sqlText);
      Vector<String> data = new Vector<String>();
      while (rs.next()) {
        data = new Vector<String>();
        data.add(rs.getString(1));
        data.add(rs.getString(2));
        data.add(rs.getString(3));
        stat.add(data);
      }
      rs.close();
      sqlStatement.close();
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, null, "ReportDAO : getStoreChartStat" + ex);
      throw new KwickKioskDBException("ReportDAO : getStoreChartStat",
                    ex.getMessage());
    }

    return stat;
  }

  /**
   * This method retrieves data required to view chart for store
   * @param store string for name of store
   * @param startDate  a string for start date
   * @param endDate a string
   * @return a vectored object as result
   * @throws {@link KwickKioskDBException}
   */
  public Vector<Vector<String>> getStoreChartMthStat(String store,
                                                     String startDate,
                                                     String endDate)
    throws KwickKioskDBException {

    Statement sqlStatement;
    Vector<Vector<String>> stat = new Vector<Vector<String>>();
    try {
      sqlStatement = conn.createStatement();
      String sqlText = "select o.storeName, month(o.orderDate) as mth, "
        + "year(o.orderDate) as yr, sum(io.soldPrice) as sales "
        + "from item i, inorder io, orders o "
        + "where i.upc=io.upc and io.oid=o.oid "
        + "and o.storeName='" + store + "' "
        + "and '" + startDate + "' <= o.orderDate and o.orderDate <= "
        + "'" + endDate + "' group by mth, yr";
      ResultSet rs = sqlStatement.executeQuery(sqlText);
      Vector<String> data = new Vector<String>();
      while (rs.next()) {
        data = new Vector<String>();
        data.add(rs.getString(1));
        data.add(rs.getString(2));
        data.add(rs.getString(3));
        data.add(rs.getString(4));
        stat.add(data);
      }
      rs.close();
      sqlStatement.close();
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, null, "ReportDAO : getStoreChartMthStat" + ex);
      throw new KwickKioskDBException("ReportDAO : getStoreChartMthStat",
                    ex.getMessage());
    }

    return stat;
  }

  /**
   * This method retrieves data required to view chart for store
   * @param store string for name of store
   * @param startDate a string for start date
   * @param endDate a string
   * @return a vectored object as result
   * @throws {@link KwickKioskDBException}
   */
  public Vector<Vector<String>> getStoreChartYearStat(String store,
                                                     String startDate,
                                                     String endDate)
    throws KwickKioskDBException {

    Statement sqlStatement;
    Vector<Vector<String>> stat = new Vector<Vector<String>>();
    try {
      sqlStatement = conn.createStatement();
      String sqlText = "select o.storeName, "
        + "year(o.orderDate) as yr, sum(io.soldPrice) as sales "
        + "from digistore.item i, digistore.inOrder io, digistore.orders o "
        + "where i.upc=io.upc and io.oid=o.oid "
        + "and o.storeName='" + store + "' "
        + "and '" + startDate + "' <= o.orderDate and o.orderDate <= "
        + "'" + endDate + "' group by yr";
      ResultSet rs = sqlStatement.executeQuery(sqlText);
      Vector<String> data = new Vector<String>();
      while (rs.next()) {
        data = new Vector<String>();
        data.add(rs.getString(1));
        data.add(rs.getString(2));
        data.add(rs.getString(3));
        stat.add(data);
      }
      rs.close();
      sqlStatement.close();
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, null, "ReportDAO : getStoreChartYearStat" + ex);
      throw new KwickKioskDBException("ReportDAO : getStoreChartYearStat",
                    ex.getMessage());
    }

    return stat;
   }

  /**
   * This method retrieves data required to view chart for store based on area
   * @param postCode a string for postal code
   * @param startDate a string for start date
   * @param endDate a string
   * @return a vectored object as result
   * @throws {@link KwickKioskDBException}
   */
  public Vector<Vector<String>> getAreaChartStat(String postCode,
                                                 String startDate,
                                                 String endDate)
    throws KwickKioskDBException {

    Statement sqlStatement;
    Vector<Vector<String>> stat = new Vector<Vector<String>>();
    try {
      sqlStatement = conn.createStatement();
      String sqlText = "select a.postCode, o.orderDate, "
        + "sum(io.soldPrice) as sales "
        + "from item i, inorder io, orders o, "
        + "address a "
        + "where i.upc=io.upc and io.oid=o.oid and o.aid=a.aid "
        + "and a.postCode='" + postCode + "' "
        + "and '" + startDate + "' <= o.orderDate and o.orderDate <= "
        + "'" + endDate + "' " + "group by o.orderDate";
      ResultSet rs = sqlStatement.executeQuery(sqlText);
      Vector<String> data = new Vector<String>();
      while (rs.next()) {
        data = new Vector<String>();
        data.add(rs.getString(1));
        data.add(rs.getString(2));
        data.add(rs.getString(3));
        stat.add(data);
      }
      rs.close();
      sqlStatement.close();
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, null, "ReportDAO : getAreaChartStat" + ex);
      throw new KwickKioskDBException("ReportDAO : getAreaChartStat",
                    ex.getMessage());
    }

    return stat;
  }

  /**
   * This method retrieves data required to view chart for store based on area
   * @param postCode a string for postal code
   * @param startDate a string for start date
   * @param endDate a string
   * @return a vectored object as result
   * @throws {@link KwickKioskDBException}
   */
  public Vector<Vector<String>> getAreaChartMthStat(String postCode,
                                                    String startDate,
                                                    String endDate)
    throws KwickKioskDBException {

    Statement sqlStatement;
    Vector<Vector<String>> stat = new Vector<Vector<String>>();
    try {
      sqlStatement = conn.createStatement();
      String sqlText = "select a.postCode, month(o.orderDate) as mth, "
        + "year(o.orderDate) as yr, sum(io.soldPrice) as sales "
        + "from item i, inorder io, orders o, "
        + "address a "
        + "where i.upc=io.upc and io.oid=o.oid and o.aid=a.aid "
        + "and a.postCode='" + postCode + "' "
        + "and '" + startDate + "' <= o.orderDate and o.orderDate <= "
        + "'" + endDate + "' group by mth, yr";
      ResultSet rs = sqlStatement.executeQuery(sqlText);
      Vector<String> data = new Vector<String>();
      while (rs.next()) {
        data = new Vector<String>();
        data.add(rs.getString(1));
        data.add(rs.getString(2));
        data.add(rs.getString(3));
        data.add(rs.getString(4));
        stat.add(data);
      }
      rs.close();
      sqlStatement.close();
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, null, "ReportDAO : getStoreChartMthStat" + ex);
      throw new KwickKioskDBException("ReportDAO : getStoreChartMthStat",
                    ex.getMessage());
    }

    return stat;
  }

  /**
   * This method retrieves data required to view chart for store based on area.
   * @param postCode a string for postal code
   * @param startDate a string for start date
   * @param endDate  a string
   * @return a vectored object as result
   * @throws {@link KwickKioskDBException}
   */
  public Vector<Vector<String>> getAreaChartYearStat(String postCode,
                                                    String startDate,
                                                    String endDate)
    throws KwickKioskDBException {

    Statement sqlStatement;
    Vector<Vector<String>> stat = new Vector<Vector<String>>();
    try {
      sqlStatement = conn.createStatement();
      String sqlText = "select a.postCode, "
        + "year(o.orderDate) as yr, sum(io.soldPrice) as sales "
        + "from digistore.item i, digistore.inOrder io, digistore.orders o, "
        + "digistore.address a "
        + "where i.upc=io.upc and io.oid=o.oid and o.aid=a.aid "
        + "and a.postCode='" + postCode + "' "
        + "and '" + startDate + "' <= o.orderDate and o.orderDate <= "
        + "'" + endDate + "' group by yr";
      ResultSet rs = sqlStatement.executeQuery(sqlText);
      Vector<String> data = new Vector<String>();
      while (rs.next()) {
        data = new Vector<String>();
        data.add(rs.getString(1));
        data.add(rs.getString(2));
        data.add(rs.getString(3));
        stat.add(data);
      }
      rs.close();
      sqlStatement.close();
    } catch (SQLException ex) {
      logger.log(Level.SEVERE, null, "ReportDAO : getAreaChartYearStat" + ex);
      throw new KwickKioskDBException("ReportDAO : getAreaChartYearStat",
                    ex.getMessage());
    }

    return stat;
  }

}
